﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Reflection;
using FortuneHeavenCommon.ComMethod;
using System.Text.RegularExpressions;

namespace FortuneHeavenCommon.ComMethod
{
    public class SqlBase
    {
        private string conn = "";
        public virtual string ConnectionString
        {
            get;
            set;
        }

        public List<T> Query<T>(string sql, params SqlParameter[] sqlparam) where T : new()
        {
            List<T> content = new List<T>();
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                SqlCommand command = conn.CreateCommand();
                command.CommandText = sql;
                command.CommandType = System.Data.CommandType.Text;
                if (sqlparam != null)
                {
                    foreach (SqlParameter p in sqlparam)
                    {
                        command.Parameters.Add(p);
                    }
                }
                SqlDataReader reader = command.ExecuteReader();
                Type target = typeof(T);
         
                while (reader.Read())
                {
                    T t = new T();
                    
                    for (int index = 0; index < reader.FieldCount; index++)
                    {
                       PropertyInfo infor =  target.GetProperty(reader.GetName(index));
                       if (infor != null)
                       {
                           infor.SetValue(t, SafeGet(reader.GetValue(index), infor), null);
                       }
                    }
                    content.Add(t);
                    
                }
                command.Dispose();
                conn.Close();
            }
            return content;
        }

        private object SafeGet(object t, PropertyInfo infor)
        {
            if (t is DBNull)
            {
                if (infor.PropertyType == typeof(int))
                {
                    return 0;
                }
                if (infor.PropertyType == typeof(double))
                {
                    return 0;
                }
                if (infor.PropertyType == typeof(string))
                {
                    return "";
                }
                if (infor.PropertyType == typeof(DateTime))
                {
                    return DateTime.Now;
                }
                
            }
            return t;
        }

        public int RunSQL(string sql, params SqlParameter[] sqlparam) 
        {
            try
            {
                int k = 0;
                using (SqlConnection conn = new SqlConnection(ConnectionString))
                {
                    conn.Open();
                    SqlCommand command = conn.CreateCommand();
                    command.CommandText = sql;
                    command.CommandType = System.Data.CommandType.Text;
                    if (sqlparam != null)
                    {
                        foreach (SqlParameter p in sqlparam)
                        {
                            command.Parameters.Add(p);
                        }
                    }
                    object obj = command.ExecuteScalar();
                    if (obj != null && obj is int)
                    {
                        k = (int)obj;
                    }
                    command.Dispose();
                    conn.Close();

                }
                return k;
            }
            catch
            {
                return 0;
            }
        }

        public bool RunSQLNonQuery(string sql, params SqlParameter[] sqlparam)
        {
            try
            {
                int k = 0;
                using (SqlConnection conn = new SqlConnection(ConnectionString))
                {
                    conn.Open();
                    SqlCommand command = conn.CreateCommand();
                    command.CommandText = sql;
                    command.CommandType = System.Data.CommandType.Text;
                    if (sqlparam != null)
                    {
                        foreach (SqlParameter p in sqlparam)
                        {
                            command.Parameters.Add(p);
                        }
                    }
                    object obj = command.ExecuteNonQuery();
               
                    command.Dispose();
                    conn.Close();

                }
                return true;
            }
            catch
            {
                return false;
            }
            
        }

        public List<T> QueryWithCondition<T>(string cons, params SqlParameter[] sqlparam) where T : new()
        {
            List<T> content = new List<T>();
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                SqlCommand command = conn.CreateCommand();
                Type target = typeof(T);
                command.CommandText = string.Format("select * from {0} where {1}", target.Name, cons);
                command.CommandType = System.Data.CommandType.Text;
                foreach (SqlParameter p in sqlparam)
                {
                    command.Parameters.Add(p);
                }
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    T t = new T();

                    for (int index = 0; index < reader.FieldCount; index++)
                    {
                        PropertyInfo infor = target.GetProperty(reader.GetName(index), BindingFlags.IgnoreCase);
                        if (infor != null)
                        {
                            infor.SetValue(t, reader.GetValue(index), null);
                        }
                    }
                    content.Add(t);

                }
                command.Dispose();
                conn.Close();
            }
            return content;
        }
        [Obsolete]
        public void SaveDyamically(object obj)
        {
            Type target = obj.GetType();
            Type primary = typeof(PrimaryKeyAttribute);
            Type field = typeof(TableFielddAttribute);
            PropertyInfo[] infors = target.GetProperties();
            StringBuilder builder = new StringBuilder();
            List<SqlParameter> par = new List<SqlParameter>();
            builder.Append(String.Format("Update {0} Set ", target.Name));
            PropertyInfo primarykey = null;
            foreach (PropertyInfo info in infors)
            {
                if (Attribute.IsDefined(info, field))
                {
                    builder.Append(String.Format("Value = @{0},", info.Name));
                    par.Add(new SqlParameter("@" + info.Name, info.GetValue(obj, null)));
                }
                if (Attribute.IsDefined(info, primary))
                {
                    primarykey = info;
                    par.Add(new SqlParameter("@" + info.Name, info.GetValue(obj, null)));
                }
            }
            builder.Length = builder.Length - 1;
            builder.Append(string.Format(" where {0} = @{0}", primarykey.Name));
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                SqlCommand command = conn.CreateCommand();
                command.CommandText = builder.ToString(); ;
                command.CommandType = System.Data.CommandType.Text;
                command.Parameters.AddRange(par.ToArray());
                SqlDataReader reader = command.ExecuteReader();
                command.ExecuteNonQuery();
                command.Dispose();
                conn.Close();
            }

        }

        public void Save(object obj)
        {
            Type target = obj.GetType();
            FieldInfo updaefiled = target.GetField("UPDATE_SQL");
            PropertyInfo[] infors = target.GetProperties();
            List<SqlParameter> par = new List<SqlParameter>();
            string updatesql = updaefiled.GetValue(obj).ToString();
            Regex reg = new Regex(@"(?<=@)(\w|\d)+");
            MatchCollection matchs = reg.Matches(updatesql);
            HashSet<string> container = new HashSet<string>();
            if (matchs != null)
            {
                foreach (Match ma in matchs)
                {
                    container.Add(ma.Value);
                }
            }
            foreach (PropertyInfo info in infors)
            {
                if (container.Contains(info.Name))
                {
                    par.Add(new SqlParameter("@" + info.Name, info.GetValue(obj, null)));
                }
            }

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {

                conn.Open();
                SqlCommand command = conn.CreateCommand();
                command.CommandText = updatesql;
                command.CommandType = System.Data.CommandType.Text;
                command.Parameters.AddRange(par.ToArray());
                command.ExecuteNonQuery();
                command.Dispose();
                conn.Close();

            }
        }

        [Obsolete]
        public void AddDyamically(object obj)
        {
            Type target = obj.GetType();
            Type primary = typeof(PrimaryKeyAttribute);
            Type field = typeof(TableFielddAttribute);
            PropertyInfo[] infors = target.GetProperties();
            StringBuilder builder = new StringBuilder();
            StringBuilder last = new StringBuilder();
            List<SqlParameter> par = new List<SqlParameter>();
            builder.Append(String.Format("Insert into {0} (", target.Name));
            last.Append(" values (");
            foreach (PropertyInfo info in infors)
            {
                if (Attribute.IsDefined(info, field) || !Attribute.IsDefined(info, primary))
                {
                    builder.Append(field.Name + ",");
                    last.Append(string.Format("@{0},", field.Name));
                    par.Add(new SqlParameter("@" + info.Name, info.GetValue(obj, null)));
                }
            }
            
            builder.Length = builder.Length - 1;
            last.Length = last.Length - 1;
            builder.Append(")");
            last.Append(")");
            builder.Append(last.ToString());
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                SqlCommand command = conn.CreateCommand();
                command.CommandText = builder.ToString(); ;
                command.CommandType = System.Data.CommandType.Text;
                command.Parameters.AddRange(par.ToArray());
                SqlDataReader reader = command.ExecuteReader();
                command.ExecuteNonQuery();
                command.Dispose();
                conn.Close();
            }

        }
        public void Add(object obj)
        {
            Type target = obj.GetType();
            FieldInfo updaefiled = target.GetField("INSERT_SQL");
            PropertyInfo[] infors = target.GetProperties();
            List<SqlParameter> par = new List<SqlParameter>();
            string updatesql = updaefiled.GetValue(obj).ToString();
            Regex reg = new Regex(@"(?<=@)(\w|\d)+");
            MatchCollection matchs = reg.Matches(updatesql);
            HashSet<string> container = new HashSet<string>();
            if (matchs != null)
            {
                foreach (Match ma in matchs)
                {
                    container.Add(ma.Value);
                }
            }

            foreach (PropertyInfo info in infors)
            {
                if (container.Contains(info.Name))
                {
                    par.Add(new SqlParameter("@" + info.Name, info.GetValue(obj, null)));    
                }
            }
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                SqlCommand command = conn.CreateCommand();
                command.CommandText = updaefiled.GetValue(obj).ToString();
                command.CommandType = System.Data.CommandType.Text;
                command.Parameters.AddRange(par.ToArray());
                
                command.ExecuteNonQuery();
                command.Dispose();
                conn.Close();
            }
        }
    }
}
